package database;

import java.sql.*;

import model.User1;
import oracle.jdbc.pool.OracleDataSource;

import java.util.ArrayList;


public class UserOperations 
{
	private Connection conn;
	private Statement stm;
	private ResultSet rst;
	private ResultSet rst1;
	private PreparedStatement prst;
	
	public UserOperations() 
	{
		conn=openDB();
	}
	
	// open database connection
	public Connection openDB() 
	{
		try 
		{
			OracleDataSource ods = new OracleDataSource();
			
			if ( test.Main.dataConn.equals("GaryTallaght")){
				ods.setURL("jdbc:oracle:thin:@//10.10.2.7:1521/global1");
				ods.setUser("X00106491");
				ods.setPassword("db13May85");
			}
			else if ( test.Main.dataConn.equals("KeythTallaght")){
				ods.setURL("jdbc:oracle:thin:@//10.10.2.7:1521/global1");
				ods.setUser("X00106271");
				ods.setPassword("db19Jan82");
			}
			else if ( test.Main.dataConn == "KeythHome"){
				ods.setURL("jdbc:oracle:thin:HR/localhost:1521:XE");
				ods.setUser("System");
				ods.setPassword("7481600kW");
			}
			else if ( test.Main.dataConn == "GaryHome"){
				ods.setURL("jdbc:oracle:thin:HR/SYSTEM@localhost:1521:XE");
				ods.setUser("SYSTEM");
				ods.setPassword("1nf1n1ty");
			}
			conn = ods.getConnection();
			System.out.println("\nconnected user.");
		} 
		catch (Exception e) 
		{
			System.out.print("Unable to load driver " + e);
			System.exit(1);
		}
		return conn;
	}
	
	// drop user table from database
	public void dropUserTable() 
	{
		System.out.println("Checking for existing tables.");
		try 
		{
			stm = conn.createStatement();
			try 
			{
				// Dropping a sequence
				String dropseq = "drop sequence id_seq";
				stm = conn.prepareStatement(dropseq);
				stm.executeUpdate(dropseq);
			}
				catch(SQLSyntaxErrorException e)
				{
					
				}
			try
			{
			stm.execute("DROP TABLE Orders");
			System.out.println("Order table dropped.");	
			}
			catch(SQLException ex)
			{
				
			}
			stm.execute("DROP TABLE Users");
				System.out.println("Users table dropped."); 
		} 
		catch (SQLException ex) 
		{
			System.out.println("ERROR: " + ex.getMessage());
			//ex.printStackTrace();
		}
	}
	
	// create a user table in database
	public void CreateUserTable() 
	{
		try
		{
			stm = conn.createStatement();
			String create = "CREATE TABLE Users "
					+ "(User_id NUMBER PRIMARY KEY, User_n VARCHAR(40), Pass VARCHAR(40), Qstn VARCHAR(40), Ans VARCHAR(40))";

			stm.executeUpdate(create);

			//Creating a sequence
			String createseq = "create sequence id_seq increment by 1 start with 1 nocache";
			stm = conn.createStatement();
			stm.executeUpdate(createseq);
			System.out.println("user Table created");
		} 
		catch (SQLException e)
		{
			System.out.print("SQL Exception " + e);
			System.exit(1);
		}
	}
	
	// add a User to the table
		public void addUser(User1 u)
		{
			try 
			{
				String sql = "INSERT INTO Users(User_id, User_n, Pass, Qstn, Ans ) VALUES (id_seq.nextVal,?,?,?,?)";
				
				prst = conn.prepareStatement(sql);
				prst.setString(1, u.getUsername());
				prst.setString(2, u.getPassword());
				prst.setString(3, u.getQuestion());
				prst.setString(4, u.getAnswer());
				
				prst.executeUpdate();
				System.out.println("added");
			} 
			catch (Exception se)
			{
				System.out.println(se);
			}
		}
		
		/*public ResultSet getUsername() {
			try {
				String queryString = "SELECT * FROM users";

				stm = conn.createStatement();
				rst = stm.executeQuery(queryString);
			} 
			catch (Exception e) {
				System.out.println(e);
			}
			
			return rst;
		}
		*/
		public ArrayList<String> queryUserNames() {
			ArrayList<String> names = new ArrayList<String>();
			try {
				String queryString = "SELECT User_n FROM users";
				prst = conn.prepareStatement(queryString);
				rst = prst.executeQuery();
				while (rst.next()) {
					names.add(rst.getString(1));
				}
			} catch (Exception e) {
				System.out.println(e);
			}
			return names;
		}
		
		public String getSecQ(String a) {
			String q = "blah";
			try {
				String queryString = "SELECT qstn FROM users" 
										+ " where USER_N ='" + a + "'";

				stm = conn.createStatement();
				rst1 = stm.executeQuery(queryString);
				while(rst1.next()){
					 q = rst1.getString(1);
				}
			} 
			catch (Exception e) {
				System.out.println(e);
			}
			
			return q;
		}
		public String updatePassword(String pw,String uName) {
			String ans = "blah";
			try {
				String sql = "UPDATE users SET pass='" + pw + "' where USER_N='" + uName + "'";
				stm = conn.createStatement();
				stm.executeUpdate(sql);
				String dope = "SELECT ans FROM users" 
						+ " where USER_N ='" + uName + "'";
				stm = conn.createStatement();
				rst1 = stm.executeQuery(dope);
					while(rst1.next()){
						ans = rst1.getString(1);
					}
				
			} 
			catch (Exception e) {
				System.out.println(e);
			}
			
			return ans;
		}
		
		//return admin user info
		public String[] admin()
		{
			String[] list=new String[2];
			try 
			{
				String queryString = "SELECT user_n,pass from users where user_id=1";
				stm = conn.createStatement();
				rst1 = stm.executeQuery(queryString);
				while(rst1.next())
				{
					 list[0] = rst1.getString(1);
					 list[1] = rst1.getString(2);
				}
			} 
			catch (Exception e) 
			{
				System.out.println(e);
			}
			return list;
		}
		
		//return usernames and passwords
		public ArrayList<String> userPs()
		{
			ArrayList<String> list=new ArrayList<String>();
			try 
			{
				String queryString = "SELECT user_n,pass from users";
				stm = conn.createStatement();
				rst1 = stm.executeQuery(queryString);
				while(rst1.next())
				{
					list.add(rst1.getString(1));
					list.add(rst1.getString(2));
				}
			} 
			catch (Exception e) 
			{
				System.out.println(e);
			}
			return list;
		}
		
		// close the connection to database
		public void closeDB() 
		{
			try 
			{
				stm.close();
				conn.close();
				System.out.print("Connection closed user");
			} 
			catch (SQLException e)
			{
				System.out.print("Could not close connection ");
				e.printStackTrace();
			}
		}
		
		// close the connection to database
				public void closeDB2() 
				{
					try 
					{
						//stm.close();
						conn.close();
						System.out.print("Connection closed user");
					} 
					catch (SQLException e)
					{
						System.out.print("Could not close connection ");
						e.printStackTrace();
					}
				}
}